/**  
 * 文 件 名: DBUtil.java  
 * 描    述：   
 * 版    权：Copyright (c)20012-2012 tongzhou All Rights Reserved.
 * 公    司: 同舟 
 * 作    者：彭定雄                     
 * 版    本: 1.0  
 * 创建时间: 2012-10-27
 *  
 * 修改历史：  
 * 时间                             作者                       版本                        描述  
 * ------------------------------------------------------------------  
 * 2012-10-27        彭定雄                1.0          1.0 Version  
 */        

package com.tongzhou.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.log4j.Logger;

import com.tongzhou.db.exception.DBException;


public class DBUtil {
	private static Logger log = Logger.getLogger(DBUtil.class.getName()) ;
	
	/**
	 * 更新数据，包括：插入，更新，删除 
	 *@author: 彭定雄
	 *@param conn 数据库连接
	 *@param sql 要执行的SQL语句
	 *@return
	 *@throws DBException
	 */
	public static int update(String sql)throws DBException{
		int result = 0 ;
		Connection conn = ConnectionFactory.getConnection() ; 
		try{
			QueryRunner qr = new QueryRunner();
			result = qr.update(conn,sql);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
		return result ;
	}
	
	/**
	 * 更新数据，包括：插入，更新，删除 
	 *@author: 彭定雄
	 *@param conn 数据库连接
	 *@param sql 要执行的SQL语句
	 *@param params 参数集
	 *@return
	 *@throws DBException
	 */
	public static int update(String sql,Object[] params)throws DBException{
		if(params==null||params.length<=0){
			return update(sql) ;
		}
		int result = 0 ;
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			result = qr.update(conn,sql,params);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
		return result ;
	}
	
	
	/**
	 * 
	 * 查询
	 *@author: 彭定雄
	 *@param <T>
	 *@param conn
	 *@param sql
	 *@param rsh
	 *@return
	 *@throws DBException
	 */
	public static <T> T query(String sql,ResultSetHandler<T> rsh)throws DBException{
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			return qr.query(conn, sql, rsh);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 查询 
	 *@author: 彭定雄
	 *@param <T>
	 *@param conn
	 *@param sql
	 *@param rsh
	 *@param params
	 *@return
	 *@throws DBException
	 */
	public static <T> T query(String sql,ResultSetHandler<T> rsh,Object[] params)throws DBException{
		if(params==null||params.length<=0){
			return query(sql, rsh) ;
		}
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			return qr.query(conn, sql, rsh,params);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 提取从startPos序号开始的count条记录数 
	 *@author: 彭定雄
	 *@param <T>
	 *@param conn
	 *@param sql
	 *@param rsh
	 *@param startPos
	 *@param count
	 *@return
	 *@throws DBException
	 */
	public static <T> T query(String sql,ResultSetHandler<T> rsh,int startPos,int count)throws DBException{
		Connection conn = ConnectionFactory.getConnection() ;
		sql = sql + " limit "+startPos + "," + count ;
		try{
			QueryRunner qr = new QueryRunner();
			return qr.query(conn, sql, rsh);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 
	 * 提取从startPos序号开始的count条记录数 
	 *@author: 彭定雄
	 *@param <T>
	 *@param conn
	 *@param sql
	 *@param rsh
	 *@param params
	 *@param startPos
	 *@param count
	 *@return
	 *@throws DBException
	 */
	public static <T> T query(String sql,ResultSetHandler<T> rsh,Object[] params,int startPos,int count)throws DBException{
		if(params==null||params.length<=0){
			return query(sql, rsh, startPos, count) ;
		}
		
		sql = sql + " limit "+startPos + "," + count ;
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			return qr.query(conn, sql, rsh,params);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 批量更新数据 
	 *@author: 彭定雄
	 *@param conn
	 *@param sql
	 *@param params
	 *@return
	 *@throws DBException
	 */
	public static int [] batch(String sql,Object[][]params)throws DBException{
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			return qr.batch(conn, sql, params);
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 查询记录总数
	 *@author: 彭定雄
	 *@param conn
	 *@param sql
	 *@return
	 *@throws DBException
	 */
	public static long getCount(String sql)throws DBException{
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			Map result = qr.query(conn, sql, new MapHandler()) ;
			if(result!=null&&result.size()>0){
				Object value = result.values().toArray()[0];
				return (Long.valueOf(String.valueOf(value))).longValue();
			}else{
				throw new DBException("查询记录总数时，SQL语句写错。");
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 查询记录总数
	 *@author: 彭定雄
	 *@param conn
	 *@param sql
	 *@param params
	 *@return
	 *@throws DBException
	 */
	public static long getCount(String sql,Object [] params)throws DBException{
		
		if(params==null||params.length<=0){
			return getCount(sql) ;
		}
		
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			Map result = qr.query(conn, sql, new MapHandler(),params) ;
			if(result!=null&&result.size()>0){
				Object value = result.values().toArray()[0];
				return (Long.valueOf(String.valueOf(value))).longValue();
			}else{
				throw new DBException("查询记录总数时，SQL语句写错。");
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 查询记录总数
	 *@author: 彭定雄
	 *@param conn
	 *@param sql
	 *@return
	 *@throws DBException
	 */
	public static int queryForInt(String sql, Object [] params)throws DBException{
		Connection conn = ConnectionFactory.getConnection() ;
		try{
			QueryRunner qr = new QueryRunner();
			Map result = qr.query(conn, sql, new MapHandler(), params) ;
			if(result!=null&&result.size()>0){
				Object value = result.values().toArray()[0];
				if (value == null) return 0;
				return (Integer.valueOf(String.valueOf(value))).intValue();
			}
			
			return 0;
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		}finally{
			close(conn);
		}
	}
	
	/**
	 * 
	 * 关闭数据库连接
	 *@author: 彭定雄
	 *@param conn
	 *@throws DBException
	 */
	public static void close(Connection conn)throws DBException{
		try{
			if(conn!=null){
				if(!conn.isClosed()){
					DbUtils.close(conn) ;
				}
			}
		}catch(Exception e){
			e.printStackTrace() ;
			log.error(e.getMessage()) ;
			throw new DBException(e.getMessage()+" 关闭数据连接出错！");
		}
	}
	
	/**
	 * 关闭数据库连接
	 *@author: 彭定雄
	 *@param rs
	 *@param pstmt
	 *@param conn
	 *@throws DBException
	 */
	public static void close(ResultSet rs,PreparedStatement pstmt,Connection conn)throws DBException{
		try{
			if(rs!=null){
				DbUtils.close(rs) ;
			}
			if(pstmt!=null){
				pstmt.close();
			}
			close(conn) ;
		}catch(Exception e){
			e.printStackTrace() ;
			log.error(e.getMessage()) ;
			throw new DBException(e.getMessage()+" 关闭数据连接出错！");
		}
	}
	
	/**
	 * 插入数据，返回主键
	 *@param sql
	 *@return
	 *@throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static long insertReturnKey(String sql,Object[] params) throws DBException {
		Connection	conn = ConnectionFactory.getConnection();
		try {
			QueryRunner qr = new QueryRunner();
			qr.update(conn, sql,params);
			String sql2 = "select last_insert_id()";
			Map result = qr.query(conn, sql2, new MapHandler());
			if (result != null && result.size() > 0) {
				Object value = result.values().toArray()[0];
				if (value == null){
					return 0;
				}
				return (Long.valueOf(String.valueOf(value)));
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
			throw new DBException(e.getMessage());
		} finally {
			close(conn);
		}
		return 0;
	}
}
